package com.awspaas.user.apps.cpq.util;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import com.actionsoft.bpms.commons.database.RowMap;
import com.actionsoft.bpms.util.DBSql;
import com.alibaba.fastjson.JSONObject;

public class RefreshTempDataUtils {
	public static  void upateTemp(  Map<String,Map<String, Object>> modulLists ) {
		try {
			
			 String allPTsql="select   a.APPLYNO,a.BINDID,a.DJ as JG,"
				 		+ "a.CLF,"
				 		+ "a.RGF,a.WXWG,a.CGF,a.XMCBYS,a.LR,a.LRJE,a.SBSL,a.SBSJ,a.ZZFZB,a.ZZF," + 
					 		"b.ID,b.XTMC,b.XTMH,b.MKBH,b.SL,"
					 		+ "b.DJ,"
					 		+ "b.CLF as WCLF,"
					 		+ "b.RGF as WRGF,b.WXWG as WX,b.CGF as WCGF," + 
					 		"b.ZL,b.TJ,b.ZZL,b.ZTJ,"
					 		+ "b.ZJ,"
					 		+ "b.ZCLF,"
					 		+ "b.ZGSF,b.ZWX,b.ZCGF " + 
					 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_GRIDS b " + 
					 		"where  a.BINDID=b.BINDID  and a.SFWMB='是'";
					 String allXTsql="select   a.APPLYNO,a.BINDID," + 
					 		"         b.XTMH,b.XTMC,b.DJ,b.CLF ,b.RGF ,b.WXWG ,b.CGF  " + 
					 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_SYSTEM_PRICE b " + 
					 		"where  a.BINDID=b.BINDID  and a.SFWMB='是'";
					 String paramSql="select   a.APPLYNO,a.BINDID," + 
					 		"         b.ID,b.PARMENTIER_NAME,b.PARMENTIER_NO,b.PARMENTIER_VALUE " + 
					 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS b " + 
					 		"where  a.BINDID=b.BINDID and PARMENTIER_VALUE!='' and a.SFWMB='是'";
					 List<RowMap> templateMaps=DBSql.getMaps(allPTsql);
					 List<RowMap> systemMaps=DBSql.getMaps(allXTsql);
					 List<RowMap> paramMaps=DBSql.getMaps(paramSql);
					 //组合项目模板中模块数据
					 Map<String , List<RowMap>> tempMap=new HashMap<>();
					 if(templateMaps.size()>0) {
						 for (RowMap rowMap : templateMaps) {
							 String wfno=rowMap.getString("APPLYNO");
								if(tempMap.containsKey(wfno)) {
								  List<RowMap> lists= tempMap.get(wfno);
								  lists.add(rowMap);
								  tempMap.put(wfno, lists);
								}else {
									List<RowMap> lists=new ArrayList<RowMap>();
									lists.add(rowMap);
									tempMap.put(wfno, lists);
								}
						}
					//组合项目模板中系统价格数据
						 Map<String , Map<String, RowMap>> systemMap=new HashMap<>();
						 if(systemMaps.size()>0) {
							 for (RowMap rowMap : systemMaps) {
								 String wfno=rowMap.getString("APPLYNO");
								 //String xtbh=rowMap.getString("XTMH");
								 String xtbh=rowMap.getString("XTMC");
									if(systemMap.containsKey(wfno)) {
									  Map<String, RowMap> lists= systemMap.get(wfno);
									  lists.put(xtbh, rowMap);
									  systemMap.put(wfno, lists);
									}else {
										Map<String, RowMap> lists=new HashMap<>();
										 lists.put(xtbh, rowMap);
										systemMap.put(wfno, lists);
									}
							} 
						 }
							//组合项目模板中参数数据
							 Map<String , List<RowMap>> paramMap=new HashMap<>();
							 if(paramMaps.size()>0) {
								 for (RowMap rowMap : paramMaps) {
									 String wfno=rowMap.getString("APPLYNO");
										if(paramMap.containsKey(wfno)) {
										  List<RowMap> lists= paramMap.get(wfno);
										  lists.add(rowMap);
										  paramMap.put(wfno, lists);
										}else {
											List<RowMap> lists=new ArrayList<RowMap>();
											lists.add(rowMap);
											paramMap.put(wfno, lists);
										}
								}
							 }

							 String  modulSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
							 		+ "CLF=?,"
							 		+ "RGF=?,WXWG=?,CGF=?,"
							 		+ "ZJ=?,"
								 		+ "ZL=?,TJ=?,ZZL=?,ZTJ=?,"
								 		+ "ZCLF=?,"
								 		+ "ZGSF=?,ZWX=?,ZCGF=? where MKBH=? and  ID=?";
							 String tempSql="update BO_EU_CPQ_PROJECT_MSG set "
							 		+ "XMCBYS=?,CLF=?,RGF=?,WXWG=?,CGF=?,XMZBJ=?,LRJE=?,SBSJ=?,ZZF=?,SBJZJ=? "
								 	+ " where BINDID=? ";
							 String systemSql="update BO_EU_CPQ_SYSTEM_PRICE set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,BJDJG=? "
								 		+ " where BINDID=? and XTMC=?";
						 //遍历项目模板数据，将有关联的数据进行更新
							 List<String> sqlList=new ArrayList<>();
							 List<String> sqlLists=new ArrayList<>();
						 for (String  key : tempMap.keySet()) {
							List<RowMap> lists=tempMap.get(key);
							 RowMap data=lists.get(0);
							 String BH=data.getString("APPLYNO");//项目表单编号	
							 String bindId=data.getString("BINDID");//项目表单编号	
							 String bjlx=data.getString("BJLX");//报价类型	
							 //String clf=data.getString("CLF").isEmpty()?"0":data.getString("CLF");//项目材料费
							 BigDecimal clfnum=new BigDecimal("0");	
							 
							 //String rgf=data.getString("RGF").isEmpty()?"0":data.getString("RGF");//项目人工费
							 BigDecimal rgfnum=new BigDecimal("0");				 
							// String wxf=data.getString("WXWG").isEmpty()?"0":data.getString("WXWG");//项目外协费
							 BigDecimal wxfnum=new BigDecimal("0");				 
							// String cgf=data.getString("CGF").isEmpty()?"0":data.getString("CGF");//项目采购费
							 BigDecimal cgfnum=new BigDecimal("0");	
							 String dj=data.getString("JG").isEmpty()?"0":data.getString("JG");//项目期货价							 
							 BigDecimal djnum=new BigDecimal(dj);								 
							 String xmcbfy=data.getString("XMCBYS").isEmpty()?"0":data.getString("XMCBYS");//项目成本费用
							 BigDecimal xmcbnum=new BigDecimal(xmcbfy);	
							 String isTrue="0"; 
							
							 //用户存放更新后系统各个费用的数据
							 Map<String , JSONObject> systemNews=new HashMap<>();
							for (RowMap rowMap : lists) {
								 //获取模块子表配置明细
								String BM=rowMap.getString("MKBH");//模块编码	
								String ID=rowMap.getString("ID");//模块信息子表数据ID	
								String xtbm=rowMap.getString("XTMH");//系统编码
								String xtmc=rowMap.getString("XTMC");//系统编码
								String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//系统编码	
								BigDecimal sl=new BigDecimal(SL);
								//获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
								String ZJ=rowMap.getString("ZJ").isEmpty()?"0":rowMap.getString("ZJ");//模块总价								
								String ZCLF=rowMap.getString("ZCLF").isEmpty()?"0":rowMap.getString("ZCLF");//模块总材料费								
								String ZGSF=rowMap.getString("ZGSF").isEmpty()?"0":rowMap.getString("ZGSF");//模块总人工费
								String ZWX=rowMap.getString("ZWX").isEmpty()?"0":rowMap.getString("ZWX");//模块总外协
								String ZCGF=rowMap.getString("ZCGF").isEmpty()?"0":rowMap.getString("ZCGF");//模块总采购
								BigDecimal zjold=new BigDecimal(ZJ);								
								BigDecimal zclfold=new BigDecimal(ZCLF);								
								BigDecimal zgsfold=new BigDecimal(ZGSF);
								BigDecimal zwxold=new BigDecimal(ZWX);
								BigDecimal zcgfold=new BigDecimal(ZCGF);
								
								//获取对应更新的模块信息
								if(modulLists.containsKey(BM)) {
									 isTrue="1";
									 //判断模块是否为动态计算模块
									 Map<String, Object> jsonMap=modulLists.get(BM);
									 String type=(String) jsonMap.get("SITRUE");
									
									 /**
									  * 不是动态计算模块，则计算该模块在当前项目模板的价格，计算完成后将该条数据进行更新，
									  * 并将单栋设备的中所属该模块的系统的价格和模板中的材料费、人工费、外协、采购、设备成本价格重新计算。
									  */
									 
									  if(true){
										 /**
										  *如果是动态计算模块，这需要获取该模块的所有子项，和项目所需要的参数，
										  *根据计算公式计算出模块子项的数量，在根据数据计算出模块的单价
										  */
										 System.out.println("项目模板表单编号："+BH);
										Map<String, String> pMap = new HashMap<String, String>();
										Map<String, String> noMap = new HashMap<>();
										 List<RowMap> params=paramMap.get(key);
										 for (int i = 0; i < params.size(); i++) {
												RowMap json =  params.get(i);
												String bh = json.getString("PARMENTIER_NO");
												String value =  json.getString("PARMENTIER_VALUE");
												pMap.put(bh, value);
												int num = i + 1;
												noMap.put(bh, "B$" + num);
											}
										 List<RowMap> modules=(List<RowMap>) jsonMap.get("datas");
										   Workbook workbookwl = new XSSFWorkbook();
											try {	
											    //Workbook workbookwl = new XSSFWorkbook();
												//Workbook workbook = new XSSFWorkbook();
												//新的模块物料子项的费用信息
																															
												// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。
												/**
												 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
												 */
												/**
												 * 计算物料数量和成本。
												 */
												Map<String, RowMap> materMap = new HashMap<>();
												/************************** 通过excel公式进行计算 ***********************************/
												// 创建公式评估器
												// 设置单元格值
												Sheet sheet = workbookwl.createSheet("Sheet1");
												for (int i = 0; i < params.size(); i++) {
													int num = i + 1;
													Cell cellB1 = sheet.createRow(i).createCell(1);
													RowMap  jsonObject =  params.get(i);
													String bh = (String) jsonObject.get("PARMENTIER_NO");
													cellB1.setCellValue(pMap.get(bh));											
												}
												// 将物料中的计算规则的参数替换为指定的excel参数int i = 0; i < materials.size(); i++
												 for (int i = 0; i < modules.size(); i++) {		
													 RowMap bo = modules.get(i);
													 String equation = bo.getString("WLJSGS");
													  Document document = Jsoup.parse(equation);
													  String newQuation=document.text();
													if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
														System.out.println("参数计算公式："+newQuation);
														// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
														System.out.println("参数pMap："+pMap.toString());
														for (String key1 : pMap.keySet()) {
															int num = newQuation.indexOf(key1);
															if (num > -1) {
																newQuation = newQuation.replace(key1, noMap.get(key1));
															}
														}
														System.out.println("转换后的参数计算公式："+newQuation);
														// 计算出物料数量和当前模块的物料成本价格
														FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
														Cell cellB1 = sheet.createRow( params.size()+i).createCell(2);
														cellB1.setCellFormula(newQuation);
														formulaEvaluator.evaluateFormulaCell(cellB1);
														// 获取公式计算结果
														CellValue cellValue = formulaEvaluator.evaluate(cellB1);
														System.out.println("The result of the formula is: " + cellValue.getNumberValue());
														String price1 = bo.getString("JG").isEmpty()?"0":bo.getString("JG");																
														String zl = bo.getString("WZL").isEmpty()?"0":bo.getString("WZL");
														String tj = bo.getString("WTJ").isEmpty()?"0":bo.getString("WTJ");
														String clfnew = bo.getString("WCLF").isEmpty()?"0":bo.getString("WCLF");														
														String rgfnew = bo.getString("WRGF").isEmpty()?"0":bo.getString("WRGF");
														String wxnew = bo.getString("WX").isEmpty()?"0":bo.getString("WX");
														String cgfnew = bo.getString("WCGF").isEmpty()?"0":bo.getString("WCGF");
														double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();														
														double clftotal1 = Double.parseDouble(clfnew) * cellValue.getNumberValue();														
														double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
														double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();												
														double rgftotal = Double.parseDouble(rgfnew) * cellValue.getNumberValue();
														double wxtotal = Double.parseDouble(wxnew) * cellValue.getNumberValue();
														double cgftotal = Double.parseDouble(cgfnew) * cellValue.getNumberValue();
														// 将数量和成本价格放入物料map集合中
														bo.put("SL", cellValue.getNumberValue());
														bo.put("ZJ", total1);														
														bo.put("ZZL", zltotal);
														bo.put("ZTJ", tjtotal);
														bo.put("ZCGF", clftotal1);
														bo.put("ZGSF", rgftotal);
														bo.put("ZWX", wxtotal);
														bo.put("ZCLF", cgftotal);														
												 }
													materMap.put(bo.getString("ID"), bo);
												 }
												// 计算出模块的单价，每个物料总价相加
												    double mkPrice1 = 0;												   
												    double zzlPrice = 0;
												    double zclfPrice1 = 0;												   
												    double ztjPrice = 0;
													double zcgfPrice = 0;
													double zgsfPrice = 0;
													double zwxPrice = 0;
													
													
													
													for (String wlbm : materMap.keySet()) {
														RowMap bo = materMap.get(wlbm);
														String zjString = bo.getString("ZJ");														
														String zzlString = bo.getString("ZZL");
														String ztjString = bo.getString("ZTJ");
														String zcgfString = bo.getString("ZCGF");
														String zgsfString = bo.getString("ZGSF");
														String zwxString = bo.getString("ZWX");
														String zclfString = bo.getString("ZCLF");														
														mkPrice1 = mkPrice1 + Double.parseDouble(zjString);														
														zzlPrice = zzlPrice + Double.parseDouble(zzlString);
														ztjPrice = ztjPrice + Double.parseDouble(ztjString);
														zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
														zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
														zwxPrice = zwxPrice + Double.parseDouble(zwxString);
														zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString);														
													}
													/************************** 通过excel公式进行计算 ***********************************/
													//对当前数据的进行判断，是否有直发物资运费
													
													//新模块的单价、材料费等费用。
													BigDecimal MKDJ1 = new BigDecimal(mkPrice1);// 模块单价													
													BigDecimal CLF1 = new BigDecimal(zclfPrice1);// 材料费													
													BigDecimal RGF = new BigDecimal(zgsfPrice);// 人工费
													BigDecimal WXWG = new BigDecimal(zwxPrice);// 外协费
													BigDecimal CGF = new BigDecimal(zcgfPrice);// 采购费	
													BigDecimal ZL = new BigDecimal(zzlPrice);// 重量
													BigDecimal TJ = new BigDecimal(ztjPrice);// 体积
													System.out.println("模块价格：数量："+sl+"，单价："+MKDJ1+",材料费："+CLF1+",人工费："+RGF+",外协费:"+WXWG+",采购费："+CGF);
													//计算模块在项目模板中新的总价等费用
													 BigDecimal mndj=MKDJ1.multiply(sl);//更新后的总价；													
													 BigDecimal mnclf=CLF1.multiply(sl);//更新后的材料费；																										
													 BigDecimal mnrgf=RGF.multiply(sl);//更新后的人工费；
													 BigDecimal mnwx=WXWG.multiply(sl);//更新后的外协费；
													 BigDecimal mncgf=CGF.multiply(sl);//更新后的采购费；
													 BigDecimal mnzl=ZL.multiply(sl);//更新后的采购费；
													 BigDecimal mntj=TJ.multiply(sl);//更新后的采购费；
													 /**
													  * 更新当前模块数据
													  */
													 System.out.println("模块编码："+BM);
													 System.out.println("模块id："+ID);
													System.out.println("模块总价格：数量："+sl+"，总价："+mndj+",总材料费："+mnclf+",总人工费："+mnrgf+",总外协费:"+mnwx+",总采购费："+mncgf);
													//int num=DBSql.update(modulSql,new Object[] {MKDJ1,CLF1,RGF,WXWG,CGF,mndj,ZL,TJ,mnzl,mntj,mnclf,mnrgf,mnwx,mncgf,BM,ID});
													 String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ1+"',CLF= '"+CLF1+"' ,RGF='"+RGF+"',WXWG='"+WXWG+"',"
														 		+ "CGF='"+CGF+"',ZJ='"+mndj+"',ZCLF='"+mnclf+"',ZGSF='"+mnrgf+"',ZWX='"+mnwx+"',ZCGF='"+mncgf+"'"
														 		+ " where MKBH='"+BM+"' and  ID='"+ID+"' ; ";
															//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
															sqlList.add(upSql);
													 //计算项目模板更新后的成本价格、材料费、人工费、外协、采购
													 //1、获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
													 //成本价格计算
													 //xmcbnum=xmcbnum.subtract(zjold).add(mndj);
													 //材料费计算
													 //clfnum=clfnum.subtract(zclfold).add(mnclf);													 
														clfnum=clfnum.add(mnclf);
														xmcbnum=xmcbnum.subtract(zjold).add(mndj);													
													 djnum=djnum.subtract(zjold).add(mndj);													
													 //人工费计算
													 rgfnum=rgfnum.subtract(zgsfold).add(mnrgf);
													 //外协计算
													 wxfnum=wxfnum.subtract(zwxold).add(mnwx);
													 //采购计算
													 cgfnum=cgfnum.subtract(zcgfold).add(mncgf);
													 //计算项目模板中的设备汇总价格子表中的系统价格，包括材料费、人工费、外协、采购、单价
													 //判断项目系统中是否有该系统
													 if(systemMap.containsKey(key)) {
														//获取该系统的各个费用信息进行更新
														 Map<String, RowMap> systems=systemMap.get(key);
														 if( systems.containsKey(xtmc)) {
															 
															 if ( systemNews.containsKey(xtmc)) {
																 JSONObject system=systemNews.get(xtmc);
																	String DJ=system.getString("DJ");//系统单价
																	String SCLF=system.getString("CLF");//系统材料费
																	String SRGF=system.getString("RGF");//系统人工费
																	String SWXWG=system.getString("WXWG");//系统外协费
																	String SCGF=system.getString("CGF");//系统采购费
																	BigDecimal djold=new BigDecimal(DJ);
																	BigDecimal clfold=new BigDecimal(SCLF);
																	BigDecimal gsfold=new BigDecimal(SRGF);
																	BigDecimal wxold=new BigDecimal(SWXWG);
																	BigDecimal cgfold=new BigDecimal(SCGF);
																	//计算新的系统费用信息，价格-模块总价+新的模块总价
																	//djold=djold.subtract(zjold).add(mndj);
																	//clfold=clfold.subtract(zclfold).add(mnclf);																	
																		djold=djold.subtract(zjold).add(mndj);
																		clfold=clfold.subtract(zclfold).add(mnclf);																	
																	gsfold=gsfold.subtract(zgsfold).add(mnrgf);
																	wxold=wxold.subtract(zwxold).add(mnwx);
																	cgfold=cgfold.subtract(zcgfold).add(mncgf);
																	system.put("DJ", djold);
																	system.put("CLF", clfold);
																	system.put("RGF", gsfold);
																	system.put("WXWG", wxold);
																	system.put("CGF",cgfold );
																	systemNews.put(xtmc, system);
															}else {
																RowMap systemMsg=systems.get(xtmc);
																 //.DJ,b.CLF ,b.RGF ,b.WXWG ,b.CGF
																	String DJ=systemMsg.getString("DJ");//系统单价
																	String SCLF=systemMsg.getString("CLF");//系统材料费
																	String SRGF=systemMsg.getString("RGF");//系统人工费
																	String SWXWG=systemMsg.getString("WXWG");//系统外协费
																	String SCGF=systemMsg.getString("CGF");//系统采购费
																	BigDecimal djold=new BigDecimal(DJ);
																	BigDecimal clfold=new BigDecimal(SCLF);
																	BigDecimal gsfold=new BigDecimal(SRGF);
																	BigDecimal wxold=new BigDecimal(SWXWG);
																	BigDecimal cgfold=new BigDecimal(SCGF);
																	//计算新的系统费用信息，价格-模块总价+新的模块总价
																	//djold=djold.subtract(zjold).add(mndj);
																	//clfold=clfold.subtract(zclfold).add(mnclf);																	
																		djold=djold.subtract(zjold).add(mndj);
																		clfold=clfold.subtract(zclfold).add(mnclf);																	
																	gsfold=gsfold.subtract(zgsfold).add(mnrgf);
																	wxold=wxold.subtract(zwxold).add(mnwx);
																	cgfold=cgfold.subtract(zcgfold).add(mncgf);
																	JSONObject jsonObject=new JSONObject();
																	jsonObject.put("DJ", djold);
																	jsonObject.put("WGXS", systemMsg.getString("WGXS").isEmpty()?"0":systemMsg.getString("WGXS"));//外购系数
																	jsonObject.put("ZZXS", systemMsg.getString("ZZXS").isEmpty()?"0":systemMsg.getString("ZZXS"));//外购系数
																	jsonObject.put("CLF", clfold);
																	jsonObject.put("RGF", gsfold);
																	jsonObject.put("WXWG", wxold);
																	jsonObject.put("CGF",cgfold );
																	jsonObject.put("XTMH", xtbm);
																	jsonObject.put("XTMC", xtmc);
																	jsonObject.put("BINDID", rowMap.getString("BINDID"));
																	systemNews.put(xtmc, jsonObject);															
															}																				 																				
														 }									 									
													 }	
											} catch (Exception e) {
												throw e;
											}finally {
												try {
													workbookwl.close();
												} catch (IOException e) {
													// TODO Auto-generated catch block
													e.printStackTrace();
												}
											} 	 
									 }	
											
								}																		
							}
							//更新项目模板费用数据。
							 if(isTrue.equals("1")) {
								//计算新的制造费，材料费+人工费，乘以制造费比例
								 String zzfsl=data.getString("ZZFZB").isEmpty()?"0":data.getString("ZZFZB");//制造费比例
								 String zzf=data.getString("ZZF").isEmpty()?"0":data.getString("ZZF");//制造费比例
								 String XMZBJ=data.getString("XMZBJ").isEmpty()?"0":data.getString("XMZBJ");//项目基准价
								 String YSF=data.getString("YSFY").isEmpty()?"0":data.getString("YSFY");//项目运输费
								 BigDecimal bl=new BigDecimal(zzfsl);	
								 BigDecimal zzfOld=new BigDecimal(zzf);	
								 BigDecimal num2=new BigDecimal("100");
								 BigDecimal zzfNew=clfnum.add(rgfnum).multiply(bl.divide(num2));
								 //xmcbnum=xmcbnum.subtract(zzfOld).add(zzfNew);
								 String LR=data.getString("LR");//设备利润率
								 String LRJE=data.getString("LRJE");//设备利润金额
								 String SBSL=data.getString("SBSL");//设备税率
								 String SBSJ=data.getString("SBSJ");//设备税金
								 BigDecimal lr=new BigDecimal(LR);	
								 BigDecimal sblrje=new BigDecimal(LRJE);	
								 BigDecimal sbsl=new BigDecimal(SBSL);	
								 BigDecimal sbsj=new BigDecimal(SBSJ);	
								 BigDecimal jzj=new BigDecimal(XMZBJ);	
								 BigDecimal xmcbOld=new BigDecimal(xmcbfy);	
								 BigDecimal num=new BigDecimal("1");
								
								 //新的设备利润 成本+制造费，除以（1-利润率）x利润率
								 BigDecimal sbcblrNew= xmcbnum.add(zzfNew).divide(num.subtract(lr.divide(num2)),4 ).multiply(lr.divide(num2));
								 //新的设备税金
								 BigDecimal sjnew = xmcbnum.add(zzfNew).add(sbcblrNew).multiply(sbsl.divide(num2));
								 //设备基准价
								 BigDecimal sbjzj = xmcbnum.add(zzfNew).add(sbcblrNew).add(sjnew);
								 /**
								  * DJ=?,LSJ=?,BGLSJ=?,RGLSJ=?,JGLSJ=?,QTJN=?,QTJGT=?,RLJ=?,"
							 		+ "XMCBYS=?,CLF=?,RGF=?,WXWG=?,CGF=?,XMZBJ=?,LRJE=?,SBSJ=?,ZZF=?,SBJZJ=? "
								 	+ " where BINDID=? ";
								  */
								 jzj=jzj.subtract(sblrje).subtract(sbsj).subtract(xmcbOld).subtract(zzfOld).add(zzfNew).add(xmcbnum).add(sjnew).add(sbcblrNew);
								 /*
								  * String tempSql="update BO_EU_CPQ_PROJECT_MSG set XMCBYS=?,CLF=?,RGF=?,WXWG=?,CGF=?,XMZBJ=?,LRJE=?,SBSJ=?,ZZF=?"
								 		+ " where BINDID=? ";
								  */	
								 System.out.println("djnum:"+djnum);
								 DBSql.update(tempSql, new Object[] {
										 xmcbnum,clfnum,rgfnum,wxfnum,cgfnum,jzj,sbcblrNew,sjnew,zzfNew,sbjzj,bindId});	
								 if(!systemNews.isEmpty()) {
									 for (String  bm : systemNews.keySet()) {
										 JSONObject json=systemNews.get(bm);
										 System.out.println("单栋系统信息："+json.toJSONString());
										 String wx=json.getString("WXWG").isEmpty()?"0":json.getString("WXWG");
										 String wg=json.getString("CGF").isEmpty()?"0":json.getString("CGF");
										 String cl=json.getString("CLF").isEmpty()?"0":json.getString("CLF");
										 String rg=json.getString("RGF").isEmpty()?"0":json.getString("RGF");
										 String w=json.getString("WGXS");
										 String z=json.getString("ZZXS");
										 BigDecimal ww=new BigDecimal(w);
										 BigDecimal zz=new BigDecimal(z);
										 BigDecimal num3=new BigDecimal(wx);
										 BigDecimal num4=new BigDecimal(wg);
										 BigDecimal num5=new BigDecimal(wx);
										 BigDecimal num6=new BigDecimal(wg);
										 BigDecimal zj= num3.add(num4).multiply(ww).add(num5.add(num6).multiply(ww));
										 //DBSql.update(systemSql, new Object[] {json.getString("DJ"),cl,rg,wx,wg,zj,bindId,json.getString("XTMC")});	
											 String upSql= "update BO_EU_CPQ_SYSTEM_PRICE set DJ= '"+json.getString("DJ")+"',CLF= '"+cl+"' ,RGF='"+rg+"',WXWG='"+wx+"',CGF='"+wg+"',BJDJG='"+zj+"' where BINDID='"+bindId+"' and XTMC='"+json.getString("XTMC")+"'; ";
												//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
												sqlLists.add(upSql);

									}
								 }
							 }
						}

                  if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
				}
				if(sqlLists.size()>0) {
					String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);					
					DBSql.batch(array2);  
				}	 
						 
					 }
		} catch (Exception e) {
			throw e;
		}
		
		
	}
	/**
	 * 原材料价格更新后，联动更新物料、模块、模板的材料价格
	 * @param modulLists
	 */
	public static  void upateTempByPorperties(  Map<String,Map<String, Object>> modulLists ) {
		 String allPTsql="select   a.APPLYNO,a.BINDID,a.CLF,a.DJ as JG,"
		 		+ "a.RGF,a.XMCBYS,a.XMZBJ,a.LR,a.LRJE,a.SBSL,a.SBSJ,a.ZZFZB,a.ZZF,a.BJLX," + 
			 		" b.ID,b.XTMC,b.XTMH,b.MKBH,b.SL,b.DJ,"
			 		+ "b.CLF as WCLF," + 
			 		"  b.ZJ,"
			 		+ "b.ZCLF,"
			 		+ "b.ZGSF " + 
			 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_GRIDS b " + 
			 		"where  a.BINDID=b.BINDID and a.SFWMB='是' ";
			 String allXTsql="select   a.APPLYNO,a.BINDID," + 
			 		"         b.XTMH,b.XTMC,b.DJ,b.CLF ,b.RGF ,b.ZZXS ,b.BJDJG  " + 
			 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_SYSTEM_PRICE b " + 
			 		"where  a.BINDID=b.BINDID and a.SFWMB='是'";
			 String paramSql="select   a.APPLYNO,a.BINDID," + 
			 		"         b.ID,b.PARMENTIER_NAME,b.PARMENTIER_NO,b.PARMENTIER_VALUE " + 
			 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS b " + 
			 		"where  a.BINDID=b.BINDID and PARMENTIER_VALUE!='' and a.SFWMB='是'";
			 List<RowMap> templateMaps=DBSql.getMaps(allPTsql);
			 List<RowMap> systemMaps=DBSql.getMaps(allXTsql);
			 List<RowMap> paramMaps=DBSql.getMaps(paramSql);
			 //组合项目模板中模块数据
			 Map<String , List<RowMap>> tempMap=new HashMap<>();
			 if(templateMaps.size()>0) {
				 for (RowMap rowMap : templateMaps) {
					 String wfno=rowMap.getString("APPLYNO");
						if(tempMap.containsKey(wfno)) {
						  List<RowMap> lists= tempMap.get(wfno);
						  lists.add(rowMap);
						  tempMap.put(wfno, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							tempMap.put(wfno, lists);
						}
				}				
			 }
			//组合项目模板中系统价格数据
			 Map<String , Map<String, RowMap>> systemMap=new HashMap<>();
			 if(systemMaps.size()>0) {
				 for (RowMap rowMap : systemMaps) {
					 String wfno=rowMap.getString("APPLYNO");
					 //String xtbh=rowMap.getString("XTMH");
					 String xtbh=rowMap.getString("XTMC");
						if(systemMap.containsKey(wfno)) {
						  Map<String, RowMap> lists= systemMap.get(wfno);
						  lists.put(xtbh, rowMap);
						  systemMap.put(wfno, lists);
						}else {
							Map<String, RowMap> lists=new HashMap<>();
							 lists.put(xtbh, rowMap);
							systemMap.put(wfno, lists);
						}
				} 
			 }
				//组合项目模板中参数数据
			 Map<String , List<RowMap>> paramMap=new HashMap<>();
			 if(paramMaps.size()>0) {
				 for (RowMap rowMap : paramMaps) {
					 String wfno=rowMap.getString("APPLYNO");
						if(paramMap.containsKey(wfno)) {
						  List<RowMap> lists= paramMap.get(wfno);
						  lists.add(rowMap);
						  paramMap.put(wfno, lists);
						}else {
							List<RowMap> lists=new ArrayList<RowMap>();
							lists.add(rowMap);
							paramMap.put(wfno, lists);
						}
				}
			 }
			 String  modulSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
			 		        + "CLF=?,"
			 		        + "ZJ=?,"
				 		    + "ZCLF=?  "
				 		    + "where MKBH=? and  ID=?";
			 String  modultrueSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
			 		    + "CLF=?,"
			 		    + "RGF=?,WXWG=?,CGF=?,"
			 		    + "ZJ=?,"
				 		+ "ZL=?,TJ=?,ZZL=?,ZTJ=?,"
				 		+ "ZCLF=?, "
				 		+ "ZGSF=?,ZWX=?,ZCGF=? where MKBH=? and  ID=?"; 
			 String tempSql="update BO_EU_CPQ_PROJECT_MSG set "
			 		        + "XMCBYS=?,CLF=?,RGF=?,ZZF=?,XMZBJ=?,LRJE=?,SBSJ=?,SBJZJ=? "
				 		    + " where BINDID=? ";
			 String systemSql="update BO_EU_CPQ_SYSTEM_PRICE set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,BJDJG=?	"
				 		+ " where BINDID=? and XTMC=?";
			 //遍历项目模板数据，将有关联的数据进行更新
			 List<String> sqlList=new ArrayList<>();
			 List<String> sqlLists=new ArrayList<>();	
			 for (String  key : tempMap.keySet()) {
				 List<RowMap> lists=tempMap.get(key);
				 RowMap data=lists.get(0);
				 String BH=data.getString("APPLYNO");//项目表单编号	
				 String bindId=data.getString("BINDID");//项目表单编号	
				 String bjlx=data.getString("BJLX");//报价类型	
				 //String clf=data.getString("CLF").isEmpty()?"0":data.getString("CLF");//项目材料费
				 BigDecimal clfnum=new BigDecimal("0");				 
				 //String rgf=data.getString("RGF").isEmpty()?"0":data.getString("RGF");//项目人工费
				 BigDecimal rgfnum=new BigDecimal("0");	
				 String dj=data.getString("JG").isEmpty()?"0":data.getString("JG");//项目期货价				
				 BigDecimal djnum=new BigDecimal(dj);					 
				 String xmcbfy=data.getString("XMCBYS").isEmpty()?"0":data.getString("XMCBYS");//项目成本费用
				 BigDecimal xmcbnum=new BigDecimal(xmcbfy);	
				 String isTrue="0"; 
				 //用户存放更新后系统各个费用的数据
				 Map<String , JSONObject> systemNews=new HashMap<>();
				 for (RowMap rowMap : lists) {
					//获取模块子表配置明细
						String BM=rowMap.getString("MKBH");//模块编码	
						String ID=rowMap.getString("ID");//模块信息子表数据ID	
						String xtbm=rowMap.getString("XTMH");//系统编码
						String xtmc=rowMap.getString("XTMC");//系统名称
						String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//系统编码	
						BigDecimal sl=new BigDecimal(SL);
						//获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
						String ZJ=rowMap.getString("ZJ").isEmpty()?"0":rowMap.getString("ZJ");//模块总价						
						BigDecimal zjold=new BigDecimal(ZJ);						
						String ZCLF=rowMap.getString("ZCLF").isEmpty()?"0":rowMap.getString("ZCLF");//模块总材料费						
						BigDecimal zclfold=new BigDecimal(ZCLF);						
						String DJ=rowMap.getString("DJ").isEmpty()?"0":rowMap.getString("DJ");//模块单价																			
						BigDecimal djold=new BigDecimal(DJ);						
						//获取对应更新的模块信息
						if(modulLists.containsKey(BM)) {
							 isTrue="1";
							 //判断模块是否为动态计算模块
							 Map<String, Object> jsonMap=modulLists.get(BM);
							 String type=(String) jsonMap.get("SITRUE");													 
							 if (true) {
								 /**
								  *如果是动态计算模块，这需要获取该模块的所有子项，和项目所需要的参数，
								  *根据计算公式计算出模块子项的数量，在根据数据计算出模块的单价
								  */
								 System.out.println("项目模板表单编号："+BH);
									Map<String, String> pMap = new HashMap<String, String>();
									Map<String, String> noMap = new HashMap<>();
									List<RowMap> params=paramMap.get(key);
									for (int i = 0; i < params.size(); i++) {
											RowMap json =  params.get(i);
											String bh = json.getString("PARMENTIER_NO");
											String value =  json.getString("PARMENTIER_VALUE");
											pMap.put(bh, value);
											int num = i + 1;
											noMap.put(bh, "B$" + num);
										}
									List<RowMap> modules=(List<RowMap>) jsonMap.get("datas");
									Workbook workbookwl = new XSSFWorkbook();
									try {	
										//新的模块物料子项的费用信息
										
										// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。
										/**
										 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
										 */
										/**
										 * 计算物料数量和成本。
										 */
										Map<String, RowMap> materMap = new HashMap<>();
										/************************** 通过excel公式进行计算 ***********************************/
										// 创建公式评估器
										// 设置单元格值
										Sheet sheet = workbookwl.createSheet("Sheet1");
										for (int i = 0; i < params.size(); i++) {
											int num = i + 1;
											Cell cellB1 = sheet.createRow(i).createCell(1);
											RowMap  jsonObject =  params.get(i);
											String bh = (String) jsonObject.get("PARMENTIER_NO");
											cellB1.setCellValue(pMap.get(bh));											
										}
										// 将物料中的计算规则的参数替换为指定的excel参数int i = 0; i < materials.size(); i++
										 for (int i = 0; i < modules.size(); i++) {		
											 RowMap bo = modules.get(i);
											 String equation = bo.getString("WLJSGS");
											  Document document = Jsoup.parse(equation);
											  String newQuation=document.text();
											if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
												System.out.println("参数计算公式："+newQuation);
												// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
												System.out.println("参数pMap："+pMap.toString());
												for (String key1 : pMap.keySet()) {
													int num = newQuation.indexOf(key1);
													if (num > -1) {
														newQuation = newQuation.replace(key1, noMap.get(key1));
													}
												}
												System.out.println("转换后的参数计算公式："+newQuation);
												// 计算出物料数量和当前模块的物料成本价格
												FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
												Cell cellB1 = sheet.createRow( params.size()+i).createCell(2);
												cellB1.setCellFormula(newQuation);
												formulaEvaluator.evaluateFormulaCell(cellB1);
												// 获取公式计算结果
												CellValue cellValue = formulaEvaluator.evaluate(cellB1);
												System.out.println("The result of the formula is: " + cellValue.getNumberValue());
												String price1 = bo.getString("JG").isEmpty()?"0":bo.getString("JG");														
												String zl = bo.getString("WZL").isEmpty()?"0":bo.getString("WZL");
												String tj = bo.getString("WTJ").isEmpty()?"0":bo.getString("WTJ");
												String clfnew = bo.getString("WCLF").isEmpty()?"0":bo.getString("WCLF");												
												String rgfnew = bo.getString("WRGF").isEmpty()?"0":bo.getString("WRGF");
												String wxnew = bo.getString("WX").isEmpty()?"0":bo.getString("WX");
												String cgfnew = bo.getString("WCGF").isEmpty()?"0":bo.getString("WCGF");
												double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();												
												double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
												double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
												double clftotal1 = Double.parseDouble(clfnew) * cellValue.getNumberValue();												
												double rgftotal = Double.parseDouble(rgfnew) * cellValue.getNumberValue();
												double wxtotal = Double.parseDouble(wxnew) * cellValue.getNumberValue();
												double cgftotal = Double.parseDouble(cgfnew) * cellValue.getNumberValue();
												// 将数量和成本价格放入物料map集合中
												bo.put("SL", cellValue.getNumberValue());
												bo.put("ZJ", total1);												
												bo.put("ZZL", zltotal);
												bo.put("ZTJ", tjtotal);
												bo.put("ZCGF", cgftotal);
												bo.put("ZGSF", rgftotal);
												bo.put("ZWX", wxtotal);
												bo.put("ZCLF", clftotal1);											
										 }
											materMap.put(bo.getString("ID"), bo);
										 }
										// 计算出模块的单价，每个物料总价相加
										    double mkPrice1 = 0;		    
										    double zzlPrice = 0;
										    double ztjPrice = 0;
											double zcgfPrice = 0;
											double zgsfPrice = 0;
											double zwxPrice = 0;
											double zclfPrice1 = 0;											
											for (String wlbm : materMap.keySet()) {
												RowMap bo = materMap.get(wlbm);
												String zjString = bo.getString("ZJ");												
												String zzlString = bo.getString("ZZL");
												String ztjString = bo.getString("ZTJ");
												String zcgfString = bo.getString("ZCGF");
												String zgsfString = bo.getString("ZGSF");
												String zwxString = bo.getString("ZWX");
												String zclfString = bo.getString("ZCLF");												
												mkPrice1 = mkPrice1 + Double.parseDouble(zjString);												
												zzlPrice = zzlPrice + Double.parseDouble(zzlString);
												ztjPrice = ztjPrice + Double.parseDouble(ztjString);
												zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
												zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
												zwxPrice = zwxPrice + Double.parseDouble(zwxString);
												zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString);
												
											}
											/************************** 通过excel公式进行计算 ***********************************/
											//新模块的单价、材料费等费用。
											BigDecimal MKDJ1 = new BigDecimal(mkPrice1);// 模块单价											
											BigDecimal CGF = new BigDecimal(zcgfPrice);// 采购费	
											BigDecimal RGF = new BigDecimal(zgsfPrice);// 人工费
											BigDecimal WXWG = new BigDecimal(zwxPrice);// 外协费
											BigDecimal CLF1 = new BigDecimal(zclfPrice1);// 材料费 											
											BigDecimal ZL = new BigDecimal(zzlPrice);// 重量
											BigDecimal TJ = new BigDecimal(ztjPrice);// 体积
											System.out.println("模块价格：数量："+sl+"，单价："+MKDJ1+",材料费："+CLF1+",人工费："+RGF+",外协费:"+WXWG+",采购费："+CGF);
											//计算模块在项目模板中新的总价等费用
											 BigDecimal mndj=MKDJ1.multiply(sl);//更新后的总价；											
											 BigDecimal mnclf=CLF1.multiply(sl);//更新后的材料费；											 											
											 BigDecimal mnrgf=RGF.multiply(sl);//更新后的人工费；
											 BigDecimal mnwx=WXWG.multiply(sl);//更新后的外协费；
											 BigDecimal mncgf=CGF.multiply(sl);//更新后的采购费；
											 BigDecimal mnzl=ZL.multiply(sl);//更新后的采购费；
											 BigDecimal mntj=TJ.multiply(sl);//更新后的采购费；
											 /**
											  * 更新当前模块数据
											  */
											 System.out.println("模块编码："+BM);
											 System.out.println("模块id："+ID);
											System.out.println("模块总价格：数量："+sl+"，总价："+mndj+",总材料费："+mnclf+",总人工费："+mnrgf+",总外协费:"+mnwx+",总采购费："+mncgf);
											/**
											 * int num=DBSql.update(modultrueSql,new Object[] {MKDJ1,
											 * CLF1,
													RGF,WXWG,CGF,
													mndj,
													ZL,TJ,mnzl,mntj,
													mnclf,
													mnrgf,mnwx,mncgf,BM,ID});
											 */
													
											String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ1+"',CLF= '"+CLF1+"',RGF='"+RGF+"',WXWG='"+WXWG+"',CGF='"+CGF+"',ZJ='"+mndj+"',ZCLF='"+mnclf+"',ZGSF='"+mnrgf+"',ZWX='"+mnwx+"',ZCGF='"+mncgf+"'  where MKBH='"+BM+"' and ID='"+ID+"'; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlList.add(upSql);	
											 //计算项目模板更新后的成本价格、材料费、人工费、外协、采购
											 //1、获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
											djnum=djnum.subtract(zjold).add(mndj);																						
											 //成本价格计算
											 //xmcbnum=xmcbnum.subtract(zjold).add(mndj);
											 //材料费计算
											// clfnum=clfnum.subtract(zclfold).add(mnclf);											 
											//计算新的系统费用信息，价格-模块总价+新的模块总价

												xmcbnum=xmcbnum.subtract(zjold).add(mndj);
												 clfnum=clfnum.subtract(zclfold).add(mnclf);
											
											 //计算项目模板中的设备汇总价格子表中的系统价格，包括材料费、单价
											 //判断项目系统中是否有该系统
											 if(systemMap.containsKey(key)) {
												//获取该系统的各个费用信息进行更新
												 Map<String, RowMap> systems=systemMap.get(key);
												 if( systems.containsKey(xtmc)) {
													 if ( systemNews.containsKey(xtmc)) {
														 JSONObject system=systemNews.get(xtmc);
														 String XTJG=system.getString("DJ");//系统单价
														 String SCLF=system.getString("CLF");//系统材料费
														 BigDecimal xtold=new BigDecimal(DJ);
														 BigDecimal clfold=new BigDecimal(SCLF);
														//计算新的系统费用信息，价格-模块总价+新的模块总价
														//计算新的系统费用信息，价格-模块总价+新的模块总价														
															 xtold=xtold.subtract(zjold).add(mndj);
															clfold=clfold.subtract(zclfold).add(mnclf);														
														 //xtold=xtold.subtract(zjold).add(mndj);
														// clfold=clfold.subtract(zclfold).add(mnclf);
														 system.put("DJ", xtold);
														 system.put("CLF", clfold);
														 systemNews.put(xtmc, system);
													 }else {
														 RowMap systemMsg=systems.get(xtbm);
														 String XTJG=systemMsg.getString("DJ");//系统单价
														 String SCLF=systemMsg.getString("CLF");//系统材料费
														 String SRGF=systemMsg.getString("RGF");//系统人工费
														 String SWXWG=systemMsg.getString("WXWG");//系统外协费
														 String SCGF=systemMsg.getString("CGF");//系统采购费
														 BigDecimal xtold=new BigDecimal(DJ);
														 BigDecimal clfold=new BigDecimal(SCLF);
														 
														//计算新的系统费用信息，价格-模块总价+新的模块总价
														//计算新的系统费用信息，价格-模块总价+新的模块总价														
															 xtold=xtold.subtract(zjold).add(mndj);
															clfold=clfold.subtract(zclfold).add(mnclf);														
														 //xtold=xtold.subtract(zjold).add(mndj);
														 //clfold=clfold.subtract(zclfold).add(mnclf);
															JSONObject jsonObject=new JSONObject();
															jsonObject.put("DJ", djold);
															jsonObject.put("WGXS", systemMsg.getString("WGXS").isEmpty()?"0":systemMsg.getString("WGXS"));//外购系数
															jsonObject.put("ZZXS", systemMsg.getString("ZZXS").isEmpty()?"0":systemMsg.getString("ZZXS"));//外购系数
															jsonObject.put("CLF", clfold);
															jsonObject.put("RGF", SRGF);
															jsonObject.put("WXWG", SWXWG);
															jsonObject.put("CGF",SCGF );
															jsonObject.put("XTMH", xtbm);
															jsonObject.put("XTMC", xtmc);
															jsonObject.put("BINDID", rowMap.getString("BINDID"));
															systemNews.put(xtmc, jsonObject);						
													 }
												 }
											 }
									} catch (Exception e) {
										throw e;
									}finally {
										try {
											workbookwl.close();
										} catch (IOException e) {
											// TODO Auto-generated catch block
											e.printStackTrace();
										}
									} 
							}
						}
				 }
					//更新项目模板费用数据。
				 if(isTrue.equals("1")) {
					 //计算新的制造费，材料费+人工费，乘以制造费比例
					 String zzfsl=data.getString("ZZFZB").isEmpty()?"0":data.getString("ZZFZB");//制造费比例
					 String zzf=data.getString("ZZF").isEmpty()?"0":data.getString("ZZF");//制造费比例
					 String XMZBJ=data.getString("XMZBJ").isEmpty()?"0":data.getString("XMZBJ");//项目基准价
					 String YSF=data.getString("YSFY").isEmpty()?"0":data.getString("YSFY");//项目运输费
					 BigDecimal bl=new BigDecimal(zzfsl);	
					 BigDecimal zzfOld=new BigDecimal(zzf);	
					 BigDecimal num2=new BigDecimal("100");
					 BigDecimal zzfNew=clfnum.add(rgfnum).multiply(bl.divide(num2));
					 //xmcbnum=xmcbnum.subtract(zzfOld).add(zzfNew);
					 String LR=data.getString("LR");//设备利润率
					 String LRJE=data.getString("LRJE");//设备利润金额
					 String SBSL=data.getString("SBSL");//设备税率
					 String SBSJ=data.getString("SBSJ");//设备税金
					 BigDecimal lr=new BigDecimal(LR);	
					 BigDecimal sblrje=new BigDecimal(LRJE);	
					 BigDecimal sbsl=new BigDecimal(SBSL);	
					 BigDecimal sbsj=new BigDecimal(SBSJ);	
					 BigDecimal jzj=new BigDecimal(XMZBJ);	
					 BigDecimal xmcbOld=new BigDecimal(xmcbfy);	
					 BigDecimal num=new BigDecimal("1");
					
					 BigDecimal ysf=new BigDecimal(YSF);
					 //新的设备利润 成本+制造费，除以（1-利润率）x利润率
					 BigDecimal sbcblrNew= xmcbnum.add(zzfNew).divide(num.subtract(lr.divide(num2)),4 ).multiply(lr.divide(num2));
					 //新的设备税金
					 BigDecimal sjnew = xmcbnum.add(zzfNew).add(sbcblrNew).multiply(sbsl.divide(num2));
					 //设备基准价
					 BigDecimal sbjzj = xmcbnum.add(zzfNew).add(sbcblrNew).add(sjnew);
					 /**
					  *  DJ=?,LSJ=?,BGLSJ=?,RGLSJ=?,JGLSJ=?,QTJN=?,QTJGT=?,RLJ=?,"
			 		        + "XMCBYS=?,CLF=?,RGF=?,ZZF=?,XMZBJ=?,LRJE=?,SBSJ=?,SBJZJ=? "
				 		    + " where BINDID=? ";
					  */
					 jzj=jzj.subtract(sblrje).subtract(sbsj).subtract(xmcbOld).subtract(zzfOld).add(zzfNew).add(xmcbnum).add(sjnew).add(sbcblrNew);
					 DBSql.update(tempSql, new Object[] {xmcbnum,clfnum,rgfnum,zzfNew,jzj,sbcblrNew,sjnew,sbjzj,bindId});	
					 if(!systemNews.isEmpty()) {
						 for (String  bm : systemNews.keySet()) {
							 JSONObject json=systemNews.get(bm);
							 System.out.println("单栋系统信息："+json.toJSONString());
							 String wx=json.getString("WXWG").isEmpty()?"0":json.getString("WXWG");
							 String wg=json.getString("CGF").isEmpty()?"0":json.getString("CGF");
							 String cl=json.getString("CLF").isEmpty()?"0":json.getString("CLF");
							 String rg=json.getString("RGF").isEmpty()?"0":json.getString("RGF");
							 String w=json.getString("WGXS");
							 String z=json.getString("ZZXS");
							 BigDecimal ww=new BigDecimal(w);
							 BigDecimal zz=new BigDecimal(z);
							 BigDecimal num3=new BigDecimal(wx);
							 BigDecimal num4=new BigDecimal(wg);
							 BigDecimal num5=new BigDecimal(wx);
							 BigDecimal num6=new BigDecimal(wg);
							 BigDecimal zj= num3.add(num4).multiply(ww).add(num5.add(num6).multiply(ww));
							 //"update BO_EU_CPQ_SYSTEM_PRICE set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,BJDJG=?	"
						 	//	+ " where BINDID=? and XTMC=?"
							 //DBSql.update(systemSql, new Object[] {json.getString("DJ"),cl,rg,wx,wg,zj,bindId,json.getString("XTMC")});	
							 String upSql= "update BO_EU_CPQ_SYSTEM_PRICE set DJ= '"+json.getString("DJ")+"',CLF= '"+cl+"' ,RGF='"+rg+"',WXWG='"+wx+"',CGF='"+wg+"',BJDJG='"+zj+"' where BINDID='"+bindId+"' and XTMC='"+json.getString("XTMC")+"'; ";
								//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
								sqlLists.add(upSql);
						}
					 }
				 }
			 }
			 if(sqlList.size()>0) {
				 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
				 DBSql.batch(array3);
			}
			 if(sqlLists.size()>0) {
				 String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);	
				 DBSql.batch(array2);
			}
	}
	/**
	 * 人工单价更新后，联动更新模板的人工费价格
	 * @param modulLists
	 */
	public static void updateTempByWageRate‌(Map<String,Map<String, Object>> modulLists ){

		String allPTsql="select   a.APPLYNO,a.BINDID,a.CLF,a.DJ as JG,"
				+ "a.RGF,a.XMCBYS,a.XMZBJ,a.LR,a.LRJE,a.SBSL,a.SBSJ,a.ZZFZB,a.ZZF,a.BJLX," + 
		 		" b.ID,b.XTMC,b.XTMH,b.MKBH,b.SL,b.DJ,"
		 		+ "b.CLF as WCLF,"
		 		+"b.ZJ,"
		 		+ "b.ZCLF,"
		 		+ "b.ZGSF " + 
		 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_GRIDS b " + 
		 		"where  a.BINDID=b.BINDID and a.SFWMB='是' ";
		 String allXTsql="select   a.APPLYNO,a.BINDID," + 
		 		"         b.XTMH,b.XTMC,b.DJ,b.CLF ,b.RGF ,b.ZZXS ,b.BJDJG  " + 
		 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_SYSTEM_PRICE b " + 
		 		"where  a.BINDID=b.BINDID and a.SFWMB='是'";
		 String paramSql="select   a.APPLYNO,a.BINDID," + 
		 		"         b.ID,b.PARMENTIER_NAME,b.PARMENTIER_NO,b.PARMENTIER_VALUE " + 
		 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS b " + 
		 		"where  a.BINDID=b.BINDID and PARMENTIER_VALUE!='' and a.SFWMB='是'";
		 List<RowMap> templateMaps=DBSql.getMaps(allPTsql);
		 List<RowMap> systemMaps=DBSql.getMaps(allXTsql);
		 List<RowMap> paramMaps=DBSql.getMaps(paramSql);
		 //组合项目模板中模块数据
		 Map<String , List<RowMap>> tempMap=new HashMap<>();
		 if(templateMaps.size()>0) {
			 for (RowMap rowMap : templateMaps) {
				 String wfno=rowMap.getString("APPLYNO");
					if(tempMap.containsKey(wfno)) {
					  List<RowMap> lists= tempMap.get(wfno);
					  lists.add(rowMap);
					  tempMap.put(wfno, lists);
					}else {
						List<RowMap> lists=new ArrayList<RowMap>();
						lists.add(rowMap);
						tempMap.put(wfno, lists);
					}
			}				
		 }
		//组合项目模板中系统价格数据
		 Map<String , Map<String, RowMap>> systemMap=new HashMap<>();
		 if(systemMaps.size()>0) {
			 for (RowMap rowMap : systemMaps) {
				 String wfno=rowMap.getString("APPLYNO");
				 //String xtbh=rowMap.getString("XTMH");
				 String xtbh=rowMap.getString("XTMC");
					if(systemMap.containsKey(wfno)) {
					  Map<String, RowMap> lists= systemMap.get(wfno);
					  lists.put(xtbh, rowMap);
					  systemMap.put(wfno, lists);
					}else {
						Map<String, RowMap> lists=new HashMap<>();
						 lists.put(xtbh, rowMap);
						systemMap.put(wfno, lists);
					}
			} 
		 }
			//组合项目模板中参数数据
		 Map<String , List<RowMap>> paramMap=new HashMap<>();
		 if(paramMaps.size()>0) {
			 for (RowMap rowMap : paramMaps) {
				 String wfno=rowMap.getString("APPLYNO");
					if(paramMap.containsKey(wfno)) {
					  List<RowMap> lists= paramMap.get(wfno);
					  lists.add(rowMap);
					  paramMap.put(wfno, lists);
					}else {
						List<RowMap> lists=new ArrayList<RowMap>();
						lists.add(rowMap);
						paramMap.put(wfno, lists);
					}
			}
		 }
		 String  modulSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
		 		+ "RGF=?,"
		 		+ "ZJ=?,"
			 		+ "ZGSF=? where MKBH=? and  ID=?";
		 //String  modultrueSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,LSJ=?,BGLSJ=?,RGLSJ=?,JGLSJ=?,QTJN=?,QTJGT=?,RLJ=?,"
		 		//+ "RGF=?,"
			 		//+ " where MKBH=? and  ID=?";
		 String tempSql="update BO_EU_CPQ_PROJECT_MSG set DJ=?,"
		 		+ "XMCBYS=?,CLF=?,RGF=?,ZZF=?,XMZBJ=?,LRJE=?,SBSJ=?,SBJZJ=?"
			 		+ " where BINDID=? ";
		 String systemSql="update BO_EU_CPQ_SYSTEM_PRICE set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,BJDJG=?		"
			 		+ " where BINDID=? and XTMC=?";
		 //遍历项目模板数据，将有关联的数据进行更新
		 List<String> sqlList=new ArrayList<>();
		 List<String> sqlLists=new ArrayList<>();
		 for (String  key : tempMap.keySet()) {
			 List<RowMap> lists=tempMap.get(key);
			 RowMap data=lists.get(0);
			 String BH=data.getString("APPLYNO");//项目表单编号	
			 String bindId=data.getString("BINDID");//项目表单编号	
			 
			 //String clf=data.getString("CLF").isEmpty()?"0":data.getString("CLF");//项目材料费
			 BigDecimal clfnum=new BigDecimal("0");				 
			// String rgf=data.getString("RGF").isEmpty()?"0":data.getString("RGF");//项目人工费
			 BigDecimal rgfnum=new BigDecimal("0");	
			 String bjlx=data.getString("BJLX");//报价类型	
			 String dj=data.getString("JG").isEmpty()?"0":data.getString("JG");//项目期货价	 
			 BigDecimal djnum=new BigDecimal(dj);				
			 String xmcbfy=data.getString("XMCBYS").isEmpty()?"0":data.getString("XMCBYS");//项目成本费用
			 BigDecimal xmcbnum=new BigDecimal(xmcbfy);	
			 String isTrue="0"; 
			 //用户存放更新后系统各个费用的数据
			 Map<String , JSONObject> systemNews=new HashMap<>();
			 for (RowMap rowMap : lists) {
				//获取模块子表配置明细
					String BM=rowMap.getString("MKBH");//模块编码	
					String ID=rowMap.getString("ID");//模块信息子表数据ID	
					String xtbm=rowMap.getString("XTMH");//系统编码
					String xtmc=rowMap.getString("XTMC");//系统名称
					String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//系统编码	
					BigDecimal sl=new BigDecimal(SL);
					//获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
					String ZJ=rowMap.getString("ZJ").isEmpty()?"0":rowMap.getString("ZJ");//模块总价
					String ZCLF1=rowMap.getString("ZGSF").isEmpty()?"0":rowMap.getString("ZGSF");//模块总人工费
					String DJ=rowMap.getString("DJ").isEmpty()?"0":rowMap.getString("DJ");//模块单价												
					BigDecimal zjold=new BigDecimal(ZJ);					
					BigDecimal zclfold1=new BigDecimal(ZCLF1);
					BigDecimal djold=new BigDecimal(DJ);					
					//获取对应更新的模块信息
					if(modulLists.containsKey(BM)) {
						 isTrue="1";
						 //判断模块是否为动态计算模块
						 Map<String, Object> jsonMap=modulLists.get(BM);
						 String type=(String) jsonMap.get("SITRUE");
						 if (true) {
							 /**
							  *如果是动态计算模块，这需要获取该模块的所有子项，和项目所需要的参数，
							  *根据计算公式计算出模块子项的数量，在根据数据计算出模块的单价
							  */
							 System.out.println("项目模板表单编号："+BH);
								Map<String, String> pMap = new HashMap<String, String>();
								Map<String, String> noMap = new HashMap<>();
								List<RowMap> params=paramMap.get(key);
								for (int i = 0; i < params.size(); i++) {
										RowMap json =  params.get(i);
										String bh = json.getString("PARMENTIER_NO");
										String value =  json.getString("PARMENTIER_VALUE");
										pMap.put(bh, value);
										int num = i + 1;
										noMap.put(bh, "B$" + num);
									}
								List<RowMap> modules=(List<RowMap>) jsonMap.get("datas");
								Workbook workbookwl = new XSSFWorkbook();
								try {
                                    //新的模块物料子项的费用信息
									
									// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。
									/**
									 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
									 */
									/**
									 * 计算物料数量和成本。
									 */
									Map<String, RowMap> materMap = new HashMap<>();
									/************************** 通过excel公式进行计算 ***********************************/
									// 创建公式评估器
									// 设置单元格值
									Sheet sheet = workbookwl.createSheet("Sheet1");
									for (int i = 0; i < params.size(); i++) {
										int num = i + 1;
										Cell cellB1 = sheet.createRow(i).createCell(1);
										RowMap  jsonObject =  params.get(i);
										String bh = (String) jsonObject.get("PARMENTIER_NO");
										cellB1.setCellValue(pMap.get(bh));												
									}
									// 将物料中的计算规则的参数替换为指定的excel参数int i = 0; i < materials.size(); i++
									 for (int i = 0; i < modules.size(); i++) {		
										 RowMap bo = modules.get(i);
										 String equation = bo.getString("WLJSGS");
										  Document document = Jsoup.parse(equation);
										  String newQuation=document.text();
										if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
											System.out.println("参数计算公式："+newQuation);
											// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
											System.out.println("参数pMap："+pMap.toString());
											for (String key1 : pMap.keySet()) {
												int num = newQuation.indexOf(key1);
												if (num > -1) {
													newQuation = newQuation.replace(key1, noMap.get(key1));
												}
											}
											System.out.println("转换后的参数计算公式："+newQuation);
											// 计算出物料数量和当前模块的物料成本价格
											FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
											Cell cellB1 = sheet.createRow( params.size()+i).createCell(2);
											cellB1.setCellFormula(newQuation);
											formulaEvaluator.evaluateFormulaCell(cellB1);
											// 获取公式计算结果
											CellValue cellValue = formulaEvaluator.evaluate(cellB1);
											System.out.println("The result of the formula is: " + cellValue.getNumberValue());
											String price1 = bo.getString("JG").isEmpty()?"0":bo.getString("JG");
											
											String zl = bo.getString("WZL").isEmpty()?"0":bo.getString("WZL");
											String tj = bo.getString("WTJ").isEmpty()?"0":bo.getString("WTJ");
											String clfnew = bo.getString("WCLF").isEmpty()?"0":bo.getString("WCLF");
											
											String rgfnew = bo.getString("WRGF").isEmpty()?"0":bo.getString("WRGF");
											String wxnew = bo.getString("WX").isEmpty()?"0":bo.getString("WX");
											String cgfnew = bo.getString("WCGF").isEmpty()?"0":bo.getString("WCGF");
											double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();
											
											double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
											double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
											double clftotal1 = Double.parseDouble(clfnew) * cellValue.getNumberValue();
											
											double rgftotal = Double.parseDouble(rgfnew) * cellValue.getNumberValue();
											double wxtotal = Double.parseDouble(wxnew) * cellValue.getNumberValue();
											double cgftotal = Double.parseDouble(cgfnew) * cellValue.getNumberValue();
											// 将数量和成本价格放入物料map集合中
											bo.put("SL", cellValue.getNumberValue());
											bo.put("ZJ", total1);
											bo.put("ZZL", zltotal);
											bo.put("ZTJ", tjtotal);
											bo.put("ZCLF",clftotal1 );
											bo.put("ZGSF", rgftotal);
											bo.put("ZWX", wxtotal);
											bo.put("ZCGF", cgftotal);
										}
										materMap.put(bo.getString("ID"), bo);
									 }
									// 计算出模块的单价，每个物料总价相加
									    double mkPrice1 = 0;									    
									    double zzlPrice = 0;
									    double ztjPrice = 0;
										double zcgfPrice = 0;
										double zgsfPrice = 0;
										double zwxPrice = 0;
										double zclfPrice1 = 0;										
										for (String wlbm : materMap.keySet()) {
											RowMap bo = materMap.get(wlbm);
											String zjString = bo.getString("ZJ");
											
											String zzlString = bo.getString("ZZL");
											String ztjString = bo.getString("ZTJ");
											String zcgfString = bo.getString("ZCGF");
											String zgsfString = bo.getString("ZGSF");
											String zwxString = bo.getString("ZWX");
											String zclfString = bo.getString("ZCLF");											
											mkPrice1 = mkPrice1 + Double.parseDouble(zjString);											
											zzlPrice = zzlPrice + Double.parseDouble(zzlString);
											ztjPrice = ztjPrice + Double.parseDouble(ztjString);
											zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
											zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
											zwxPrice = zwxPrice + Double.parseDouble(zwxString);
											zclfPrice1 = zclfPrice1 + Double.parseDouble(zclfString);											
										}
										/************************** 通过excel公式进行计算 ***********************************/
										//新模块的单价、材料费等费用。
										BigDecimal MKDJ1 = new BigDecimal(mkPrice1);// 模块单价										
										BigDecimal CGF = new BigDecimal(zcgfPrice);// 采购费	
										BigDecimal RGF = new BigDecimal(zgsfPrice);// 人工费
										BigDecimal WXWG = new BigDecimal(zwxPrice);// 外协费
										BigDecimal CLF1 = new BigDecimal(zclfPrice1);//材料费 										
										BigDecimal ZL = new BigDecimal(zzlPrice);// 重量
										BigDecimal TJ = new BigDecimal(ztjPrice);// 体积
										System.out.println("模块价格：数量："+sl+"，单价："+MKDJ1+",材料费："+CLF1+",人工费："+RGF+",外协费:"+WXWG+",采购费："+CGF);
										//计算模块在项目模板中新的总价等费用
										 BigDecimal mndj=MKDJ1.multiply(sl);//更新后的总价；										 
										 BigDecimal mnclf=CLF1.multiply(sl);//更新后的材料费；										 
										 BigDecimal mnrgf=RGF.multiply(sl);//更新后的人工费；
										 BigDecimal mnwx=WXWG.multiply(sl);//更新后的外协费；
										 BigDecimal mncgf=CGF.multiply(sl);//更新后的采购费；
										 BigDecimal mnzl=ZL.multiply(sl);//更新后的重量；
										 BigDecimal mntj=TJ.multiply(sl);//更新后的体积；
										 /**
										  * 更新当前模块数据
										  */
										 System.out.println("模块编码："+BM);
										 System.out.println("模块id："+ID);
										System.out.println("模块总价格：数量："+sl+"，总价："+mndj+",总材料费："+mnclf+",总人工费："+mnrgf+",总外协费:"+mnwx+",总采购费："+mncgf);
										/**
										 * "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,LSJ=?,BGLSJ=?,RGLSJ=?,JGLSJ=?,QTJN=?,QTJGT=?,RLJ=?,"
		 		                           + "RGF=?,"
		 		                       + "ZJ=?,ZLSJ=?,ZBGLSJ=?,ZJGLSJ=?,ZRGLSJ=?,ZQTJN=?,ZQTJGT=?,ZRKJ=?,"
			 		+ "ZGSF=? where MKBH=? and  ID=?";
										 */
										 //DBSql.update(modulSql,new Object[] {MKDJ1,RGF,mndj,mnrgf,BM,ID});
										 String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ1+"',RGF= '"+RGF+"' ,ZJ='"+mndj+"',ZGSF='"+mnrgf+"' where MKBH='"+BM+"' and ID='"+ID+"' ; ";
											//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
											sqlList.add(upSql);
										//int num=DBSql.update(modulSql,new Object[] {MKDJ1,CLF,RGF,WXWG,CGF,mndj,ZL,TJ,mnzl,mntj,mnclf,mnrgf,mnwx,mncgf,BM,ID});
										 //计算项目模板更新后的成本价格、材料费、人工费、外协、采购
										 //1、获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
										 //计算各个类型的成本价
										 djnum=djnum.subtract(zjold).add(mndj);
										 
										 //成本价格计算
										 
											clfnum=clfnum.add(mnclf);
											xmcbnum=xmcbnum.subtract(zjold).add(mndj);
										
										 
										 //人工费计算
										 rgfnum=rgfnum.subtract(zclfold1).add(mnrgf);
										 //计算项目模板中的设备汇总价格子表中的系统价格，包括材料费、单价
										 //判断项目系统中是否有该系统
										 if(systemMap.containsKey(key)) {
											//获取该系统的各个费用信息进行更新
											 Map<String, RowMap> systems=systemMap.get(key);
											 if( systems.containsKey(xtmc)) {
												 if ( systemNews.containsKey(xtmc)) {
													 JSONObject system=systemNews.get(xtmc);
													 String XTJG=system.getString("DJ");//系统单价
													 String SRGF=system.getString("RGF");//系统人工费
													 BigDecimal xtold=new BigDecimal(DJ);
													 BigDecimal rgfold=new BigDecimal(SRGF);
													//计算新的系统费用信息，价格-模块总价+新的模块总价

													 
														 xtold=xtold.subtract(zjold).add(mndj);
														//clfold=clfold.subtract(zclfold).add(mnclf);
													
													 rgfold=rgfold.subtract(zclfold1).add(mnclf);
													 system.put("DJ", xtold);
													 system.put("RGF", rgfold);
													 systemNews.put(xtmc, system);
												 }else {
													 RowMap systemMsg=systems.get(xtmc);
													 String XTJG=systemMsg.getString("DJ");//系统单价
													 String SCLF=systemMsg.getString("CLF");//系统材料费
													 String SRGF=systemMsg.getString("RGF");//系统人工费
													 String SWXWG=systemMsg.getString("WXWG");//系统外协费
													 String SCGF=systemMsg.getString("CGF");//系统采购费
													 BigDecimal xtold=new BigDecimal(DJ);
													 BigDecimal rgfold=new BigDecimal(SRGF);
													 
													//计算新的系统费用信息，价格-模块总价+新的模块总价
													 
														 xtold=xtold.subtract(zjold).add(mndj);
														//clfold=clfold.subtract(zclfold).add(mnclf);
													
													 
													 rgfold=rgfold.subtract(zclfold1).add(mnclf);
														JSONObject jsonObject=new JSONObject();
														jsonObject.put("DJ", djold);
														jsonObject.put("WGXS", systemMsg.getString("WGXS").isEmpty()?"0":systemMsg.getString("WGXS"));//外购系数
														jsonObject.put("ZZXS", systemMsg.getString("ZZXS").isEmpty()?"0":systemMsg.getString("ZZXS"));//外购系数
														jsonObject.put("CLF", SCLF);
														jsonObject.put("RGF", rgfold);
														jsonObject.put("WXWG", SWXWG);
														jsonObject.put("CGF",SCGF );
														jsonObject.put("XTMH", xtbm);
														jsonObject.put("XTMC", xtmc);
														jsonObject.put("BINDID", rowMap.getString("BINDID"));
														systemNews.put(xtmc, jsonObject);						
												 }
											 }
										 }
								} catch (Exception e) {
									throw e;
								}finally {
									try {
										workbookwl.close();
									} catch (IOException e) {
										// TODO Auto-generated catch block
										e.printStackTrace();
									}
								}
						 }
						 
					}
			 }
				//更新项目模板费用数据。
			 if(isTrue.equals("1")) {
				 //计算新的制造费，材料费+人工费，乘以制造费比例
				 String zzfsl=data.getString("ZZFZB").isEmpty()?"0":data.getString("ZZFZB");//制造费比例
				 String zzf=data.getString("ZZF").isEmpty()?"0":data.getString("ZZF");//制造费比例
				 String XMZBJ=data.getString("XMZBJ").isEmpty()?"0":data.getString("XMZBJ");//项目基准价
				 BigDecimal num2=new BigDecimal("100");
				 BigDecimal bl=new BigDecimal(zzfsl);	
				 BigDecimal zzfOld=new BigDecimal(zzf);	
				 BigDecimal zzfNew=clfnum.add(rgfnum).multiply(bl.divide(num2));
				 //xmcbnum=xmcbnum.subtract(zzfOld).add(zzfNew);
				 String LR=data.getString("LR");//设备利润率
				 String LRJE=data.getString("LRJE");//设备利润金额
				 String SBSL=data.getString("SBSL");//设备税率
				 String SBSJ=data.getString("SBSJ");//设备税金
				 BigDecimal lr=new BigDecimal(LR);	
				 BigDecimal sblrje=new BigDecimal(LRJE);	
				 BigDecimal sbsl=new BigDecimal(SBSL);	
				 BigDecimal sbsj=new BigDecimal(SBSJ);	
				 BigDecimal jzj=new BigDecimal(XMZBJ);	
				 BigDecimal xmcbOld=new BigDecimal(xmcbfy);	
				 BigDecimal num=new BigDecimal("1");
				
				 //新的设备利润 成本+制造费，除以（1-利润率）x利润率
				 BigDecimal sbcblrNew= xmcbnum.add(zzfNew).divide(num.subtract(lr.divide(num2)),4 ).multiply(lr.divide(num2));
				 //新的设备税金
				 BigDecimal sjnew = xmcbnum.add(zzfNew).add(sbcblrNew).multiply(sbsl.divide(num2));
				//设备基准价
				 BigDecimal sbjzj = xmcbnum.add(zzfNew).add(sbcblrNew).add(sjnew);
				 jzj=jzj.subtract(sblrje).subtract(sbsj).subtract(xmcbOld).subtract(zzfOld).add(zzfNew).add(xmcbnum).add(sjnew).add(sbcblrNew);
				 DBSql.update(tempSql, new Object[] {djnum,
						 xmcbnum,clfnum,rgfnum,zzfNew,jzj,sbcblrNew,sjnew,sbjzj,bindId});	
				 if(!systemNews.isEmpty()) {
					 for (String  bm : systemNews.keySet()) {
						 JSONObject json=systemNews.get(bm);
						 System.out.println("单栋系统信息："+json.toJSONString());
						 String wx=json.getString("WXWG").isEmpty()?"0":json.getString("WXWG");
						 String wg=json.getString("CGF").isEmpty()?"0":json.getString("CGF");
						 String cl=json.getString("CLF").isEmpty()?"0":json.getString("CLF");
						 String rg=json.getString("RGF").isEmpty()?"0":json.getString("RGF");
						 String w=json.getString("WGXS");
						 String z=json.getString("ZZXS");
						 BigDecimal ww=new BigDecimal(w);
						 BigDecimal zz=new BigDecimal(z);
						 BigDecimal num3=new BigDecimal(wx);
						 BigDecimal num4=new BigDecimal(wg);
						 BigDecimal num5=new BigDecimal(wx);
						 BigDecimal num6=new BigDecimal(wg);
						 BigDecimal zj= num3.add(num4).multiply(ww).add(num5.add(num6).multiply(ww));
						 //DBSql.update(systemSql, new Object[] {json.getString("DJ"),cl,rg,wx,wg,zj,bindId,json.getString("XTMC")});	
						 String upSql= "update BO_EU_CPQ_SYSTEM_PRICE set DJ= '"+json.getString("DJ")+"',CLF= '"+cl+"' ,RGF='"+rg+"',WXWG='"+wx+"',CGF='"+wg+"',BJDJG='"+zj+"' where BINDID='"+bindId+"' and XTMC='"+json.getString("XTMC")+"'; ";
							//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
							sqlLists.add(upSql);
					}
				 }
			 }
		 }

         if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
	}
         if(sqlLists.size()>0) {
			 String[] array2 = sqlLists.toArray(new String[sqlLists.size()]);	
			 DBSql.batch(array2);
}
		
	}
/**
 * 外协单价更新够，联动更新模板的外协费用
 * @param modulLists
 */
	public static void updateTempByOutsourcing‌(Map<String,Map<String, Object>> modulLists ){
		try {
			  String allPTsql="select   a.APPLYNO,a.BINDID,a.WXWG,a.CGF,a.DJ as JG,"
			  		+ "a.XMCBYS,a.XMZBJ,a.LR,a.LRJE,a.SBSL,a.SBSJ,a.ZZFZB,a.ZZF,a.BJLX," + 
				 		"         b.ID,b.XTMC,b.XTMH,b.MKBH,b.SL,b.DJ,"
				 		+ "b.CLF as WCLF,"
				 		+ "b.WXWG as WX," + 
				 		"		  b.ZJ,"
				 		+ "b.ZCLF,"
				 		+ "b.ZWX,b.ZCGF " + 
				 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_GRIDS b " + 
				 		"where  a.BINDID=b.BINDID and a.SFWMB='是' ";
				 String allXTsql="select   a.APPLYNO,a.BINDID," + 
				 		"         b.XTMH,b.XTMC,b.DJ,b.WXWG ,b.CGF ,b.WGXS ,b.BJDJG  " + 
				 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_SYSTEM_PRICE b " + 
				 		"where  a.BINDID=b.BINDID and a.SFWMB='是'";
				 String paramSql="select   a.APPLYNO,a.BINDID," + 
				 		"         b.ID,b.PARMENTIER_NAME,b.PARMENTIER_NO,b.PARMENTIER_VALUE " + 
				 		"from  BO_EU_CPQ_PROJECT_MSG a,BO_EU_CPQ_PROJECT_MSG_PARAMETER_GRIDS b " + 
				 		"where  a.BINDID=b.BINDID and PARMENTIER_VALUE!='' and a.SFWMB='是'";
				 List<RowMap> templateMaps=DBSql.getMaps(allPTsql);
				 List<RowMap> systemMaps=DBSql.getMaps(allXTsql);
				 List<RowMap> paramMaps=DBSql.getMaps(paramSql);
				 //组合项目模板中模块数据
				 Map<String , List<RowMap>> tempMap=new HashMap<>();
				 if(templateMaps.size()>0) {
					 for (RowMap rowMap : templateMaps) {
						 String wfno=rowMap.getString("APPLYNO");
							if(tempMap.containsKey(wfno)) {
							  List<RowMap> lists= tempMap.get(wfno);
							  lists.add(rowMap);
							  tempMap.put(wfno, lists);
							}else {
								List<RowMap> lists=new ArrayList<RowMap>();
								lists.add(rowMap);
								tempMap.put(wfno, lists);
							}
					}				
				 }
				//组合项目模板中系统价格数据
				 Map<String , Map<String, RowMap>> systemMap=new HashMap<>();
				 if(systemMaps.size()>0) {
					 for (RowMap rowMap : systemMaps) {
						 String wfno=rowMap.getString("APPLYNO");
						 //String xtbh=rowMap.getString("XTMH");
						 String xtbh=rowMap.getString("XTMC");
							if(systemMap.containsKey(wfno)) {
							  Map<String, RowMap> lists= systemMap.get(wfno);
							  lists.put(xtbh, rowMap);
							  systemMap.put(wfno, lists);
							}else {
								Map<String, RowMap> lists=new HashMap<>();
								 lists.put(xtbh, rowMap);
								systemMap.put(wfno, lists);
							}
					} 
				 }
					//组合项目模板中参数数据
				 Map<String , List<RowMap>> paramMap=new HashMap<>();
				 if(paramMaps.size()>0) {
					 for (RowMap rowMap : paramMaps) {
						 String wfno=rowMap.getString("APPLYNO");
							if(paramMap.containsKey(wfno)) {
							  List<RowMap> lists= paramMap.get(wfno);
							  lists.add(rowMap);
							  paramMap.put(wfno, lists);
							}else {
								List<RowMap> lists=new ArrayList<RowMap>();
								lists.add(rowMap);
								paramMap.put(wfno, lists);
							}
					}
				 }
				 String  modulSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
				 		+ "WXWG=?,ZJ=?,"
					 		+ "ZWX=? where MKBH=? and  ID=?";
				 String  modultrueSql="update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ=?,"
				 		+ "CLF=?,RGF=?,WXWG=?,CGF=?,ZJ=?,"
					 		+ "ZL=?,TJ=?,ZZL=?,ZTJ=?,ZCLF=?,ZGSF=?,ZWX=?,ZCGF=? where MKBH=? and  ID=?";
				 String tempSql="update BO_EU_CPQ_PROJECT_MSG set "
				 		+ "XMCBYS=?,WXWG=?,XMZBJ=?,LRJE=?,SBSJ=?,SBJZJ=? "
					 		+ " where BINDID=? ";
				 String systemSql="update BO_EU_CPQ_SYSTEM_PRICE set DJ=?,CLF=?,RGF=?,WXWG=?,CGF=?,BJDJG=? "
					 		+ " where BINDID=? and XTMC=?";
				 //遍历项目模板数据，将有关联的数据进行更新
				 List<String> sqlList=new ArrayList<>();
				 List<String> sqlLists=new ArrayList<>();
				 for (String  key : tempMap.keySet()) {
					 List<RowMap> lists=tempMap.get(key);
					 RowMap data=lists.get(0);
					 String BH=data.getString("APPLYNO");//项目表单编号	
					 String bindId=data.getString("BINDID");//项目表单编号	
					 String bjlx=data.getString("BJLX");//报价类型	
					 BigDecimal clfnum=new BigDecimal("0");	
					 String wxf=data.getString("WXWG").isEmpty()?"0":data.getString("WXWG");//项目外协费
					 BigDecimal wxfnum=new BigDecimal(wxf);				 
					 //String cgf=data.getString("CGF").isEmpty()?"0":data.getString("CGF");//项目采购费
					 BigDecimal cgfnum=new BigDecimal("0");		
					 String xmcbfy=data.getString("XMCBYS").isEmpty()?"0":data.getString("XMCBYS");//项目成本费用
					 BigDecimal xmcbnum=new BigDecimal(xmcbfy);	
					 String dj=data.getString("JG").isEmpty()?"0":data.getString("JG");//项目期货价					 
					 BigDecimal djnum=new BigDecimal(dj);						
					 String isTrue="0"; 
					 //用户存放更新后系统各个费用的数据
					 Map<String , JSONObject> systemNews=new HashMap<>();
					 for (RowMap rowMap : lists) {
						//获取模块子表配置明细
							String BM=rowMap.getString("MKBH");//模块编码	
							String ID=rowMap.getString("ID");//模块信息子表数据ID	
							String xtbm=rowMap.getString("XTMH");//系统编码
							String xtmc=rowMap.getString("XTMC");//系统名称
							String cgf=rowMap.getString("ZCGF").isEmpty()?"0":rowMap.getString("ZCGF");
							BigDecimal zcgf=new BigDecimal(cgf);
							cgfnum=cgfnum.add(zcgf);
							String SL=rowMap.getString("SL").isEmpty()?"0":rowMap.getString("SL");//系统编码	
							BigDecimal sl=new BigDecimal(SL);
							//获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
							String ZJ=rowMap.getString("ZJ").isEmpty()?"0":rowMap.getString("ZJ");//模块总价							
							String ZWX=rowMap.getString("ZWX").isEmpty()?"0":rowMap.getString("ZWX");//模块总外协费
							String DJ=rowMap.getString("DJ").isEmpty()?"0":rowMap.getString("DJ");//模块单价							
							BigDecimal zjold=new BigDecimal(ZJ);							
							BigDecimal zwxold=new BigDecimal(ZWX);
							BigDecimal djold=new BigDecimal(DJ);							
							//获取对应更新的模块信息
							if(modulLists.containsKey(BM)) {
								 isTrue="1";
								 //判断模块是否为动态计算模块
								 Map<String, Object> jsonMap=modulLists.get(BM);
								 String type=(String) jsonMap.get("SITRUE");
								 if (true) {
									 /**
									  *如果是动态计算模块，这需要获取该模块的所有子项，和项目所需要的参数，
									  *根据计算公式计算出模块子项的数量，在根据数据计算出模块的单价
									  */
									 System.out.println("项目模板表单编号："+BH);
										Map<String, String> pMap = new HashMap<String, String>();
										Map<String, String> noMap = new HashMap<>();
										List<RowMap> params=paramMap.get(key);
										for (int i = 0; i < params.size(); i++) {
												RowMap json =  params.get(i);
												String bh = json.getString("PARMENTIER_NO");
												String value =  json.getString("PARMENTIER_VALUE");
												pMap.put(bh, value);
												int num = i + 1;
												noMap.put(bh, "B$" + num);
											}
										List<RowMap> modules=(List<RowMap>) jsonMap.get("datas");
										Workbook workbookwl = new XSSFWorkbook();
										try {
		                                    //新的模块物料子项的费用信息
											
											// 计算当前模块中物料明细的数量和物料的成本价格，根据物料明细的表中的计算规则进行计算。
											/**
											 * 获取模块中的物料明细，遍历物料明细判断当前物料是否是需要计算的，是则通过计算规则进行计算，如果不是则不需要计算。
											 */
											/**
											 * 计算物料数量和成本。
											 */
											Map<String, RowMap> materMap = new HashMap<>();
											/************************** 通过excel公式进行计算 ***********************************/
											// 创建公式评估器
											// 设置单元格值
											Sheet sheet = workbookwl.createSheet("Sheet1");
											for (int i = 0; i < params.size(); i++) {
												int num = i + 1;
												Cell cellB1 = sheet.createRow(i).createCell(1);
												RowMap  jsonObject =  params.get(i);
												String bh = (String) jsonObject.get("PARMENTIER_NO");
												cellB1.setCellValue(pMap.get(bh));												
											}
											// 将物料中的计算规则的参数替换为指定的excel参数int i = 0; i < materials.size(); i++
											 for (int i = 0; i < modules.size(); i++) {		
												 RowMap bo = modules.get(i);
												 String equation = bo.getString("WLJSGS");
												  Document document = Jsoup.parse(equation);
												  String newQuation=document.text();
												if (!newQuation.equals("0")&&!newQuation.isEmpty()) {
													System.out.println("参数计算公式："+newQuation);
													// 获取物料数量计算公式中参数代号，如果有则将代号存放在list中
													System.out.println("参数pMap："+pMap.toString());
													for (String key1 : pMap.keySet()) {
														int num = newQuation.indexOf(key1);
														if (num > -1) {
															newQuation = newQuation.replace(key1, noMap.get(key1));
														}
													}
													System.out.println("转换后的参数计算公式："+newQuation);
													// 计算出物料数量和当前模块的物料成本价格
													FormulaEvaluator formulaEvaluator = workbookwl.getCreationHelper().createFormulaEvaluator();
													Cell cellB1 = sheet.createRow( params.size()+i).createCell(2);
													cellB1.setCellFormula(newQuation);
													formulaEvaluator.evaluateFormulaCell(cellB1);
													// 获取公式计算结果
													CellValue cellValue = formulaEvaluator.evaluate(cellB1);
													System.out.println("The result of the formula is: " + cellValue.getNumberValue());
													String price1 = bo.getString("JG").isEmpty()?"0":bo.getString("JG");													
													String zl = bo.getString("WZL").isEmpty()?"0":bo.getString("WZL");
													String tj = bo.getString("WTJ").isEmpty()?"0":bo.getString("WTJ");
													String clfnew = bo.getString("WCLF").isEmpty()?"0":bo.getString("WCLF");
													String rgfnew = bo.getString("WRGF").isEmpty()?"0":bo.getString("WRGF");
													String wxnew = bo.getString("WX").isEmpty()?"0":bo.getString("WX");
													String cgfnew = bo.getString("WCGF").isEmpty()?"0":bo.getString("WCGF");
													double total1 = Double.parseDouble(price1) * cellValue.getNumberValue();													
													double zltotal = Double.parseDouble(zl) * cellValue.getNumberValue();
													double tjtotal = Double.parseDouble(tj) * cellValue.getNumberValue();
													double clftotal = Double.parseDouble(clfnew) * cellValue.getNumberValue();
													double rgftotal = Double.parseDouble(rgfnew) * cellValue.getNumberValue();
													double wxtotal = Double.parseDouble(wxnew) * cellValue.getNumberValue();
													double cgftotal = Double.parseDouble(cgfnew) * cellValue.getNumberValue();
													// 将数量和成本价格放入物料map集合中
													bo.put("SL", cellValue.getNumberValue());
													bo.put("ZJ", total1);
													bo.put("ZZL", zltotal);
													bo.put("ZTJ", tjtotal);
													bo.put("ZCGF", clftotal);
													bo.put("ZGSF", rgftotal);
													bo.put("ZWX", wxtotal);
													bo.put("ZCLF", cgftotal);
												}
												materMap.put(bo.getString("ID"), bo);
											 }
											// 计算出模块的单价，每个物料总价相加
											    double mkPrice1 = 0;											    
											    double zzlPrice = 0;
											    double ztjPrice = 0;
												double zcgfPrice = 0;
												double zgsfPrice = 0;
												double zwxPrice = 0;
												double zclfPrice = 0;
												for (String wlbm : materMap.keySet()) {
													RowMap bo = materMap.get(wlbm);
													String zjString = bo.getString("ZJ");													
													String zzlString = bo.getString("ZZL");
													String ztjString = bo.getString("ZTJ");
													String zcgfString = bo.getString("ZCGF");
													String zgsfString = bo.getString("ZGSF");
													String zwxString = bo.getString("ZWX");
													String zclfString = bo.getString("ZCLF");
													mkPrice1 = mkPrice1 + Double.parseDouble(zjString);													
													zzlPrice = zzlPrice + Double.parseDouble(zzlString);
													ztjPrice = ztjPrice + Double.parseDouble(ztjString);
													zcgfPrice = zcgfPrice + Double.parseDouble(zcgfString);
													zgsfPrice = zgsfPrice + Double.parseDouble(zgsfString);
													zwxPrice = zwxPrice + Double.parseDouble(zwxString);
													zclfPrice = zclfPrice + Double.parseDouble(zclfString);
												}
												/************************** 通过excel公式进行计算 ***********************************/
												//新模块的单价、材料费等费用。
												BigDecimal MKDJ1 = new BigDecimal(mkPrice1);// 模块单价												
												BigDecimal CLF = new BigDecimal(zcgfPrice);// 材料费
												BigDecimal RGF = new BigDecimal(zgsfPrice);// 人工费
												BigDecimal WXWG = new BigDecimal(zwxPrice);// 外协费
												BigDecimal CGF = new BigDecimal(zclfPrice);// 采购费	
												BigDecimal ZL = new BigDecimal(zzlPrice);// 重量
												BigDecimal TJ = new BigDecimal(ztjPrice);// 体积
												System.out.println("模块价格：数量："+sl+"，单价："+MKDJ1+",材料费："+CLF+",人工费："+RGF+",外协费:"+WXWG+",采购费："+CGF);
												//计算模块在项目模板中新的总价等费用
												 BigDecimal mndj=MKDJ1.multiply(sl);//更新后的总价；												
												 BigDecimal mnclf=CLF.multiply(sl);//更新后的材料费；
												 BigDecimal mnrgf=RGF.multiply(sl);//更新后的人工费；
												 BigDecimal mnwx=WXWG.multiply(sl);//更新后的外协费；
												 BigDecimal mncgf=CGF.multiply(sl);//更新后的采购费；
												 BigDecimal mnzl=ZL.multiply(sl);//更新后的重量；
												 BigDecimal mntj=TJ.multiply(sl);//更新后的体积；
												 /**
												  * 更新当前模块数据
												  */
												 System.out.println("模块编码："+BM);
												 System.out.println("模块id："+ID);
												System.out.println("模块总价格：数量："+sl+"，总价："+mndj+",总材料费："+mnclf+",总人工费："+mnrgf+",总外协费:"+mnwx+",总采购费："+mncgf);
												 //DBSql.update(modulSql,new Object[] {MKDJ1,WXWG,mndj,mnwx,BM,ID}); 
												 String upSql= "update BO_EU_CPQ_PROJECT_MSG_GRIDS set DJ= '"+MKDJ1+"',WXWG= '"+WXWG+"' ,ZJ='"+mndj+"',ZWX='"+mnwx+"' where MKBH='"+BM+"' and  ID='"+ID+"' ; ";
													//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
													sqlList.add(upSql);
												//int num=DBSql.update(modultrueSql,new Object[] {MKDJ1,CLF,RGF,WXWG,CGF,mndj,ZL,TJ,mnzl,mntj,mnclf,mnrgf,mnwx,mncgf,BM,ID});
												 //计算项目模板更新后的成本价格、材料费、人工费、外协、采购
												 //1、获取该条模块该该项目的原始数据，总价、总材料费、总人工费、总外协、总采购
												 //成本价格计算
												 xmcbnum=xmcbnum.subtract(zjold).add(mndj);												 
												 //外协费计算
												 wxfnum=wxfnum.subtract(zwxold).add(mnwx);
												 //计算项目模板中的设备汇总价格子表中的系统价格，包括外协费、单价												
												 //判断项目系统中是否有该系统
												 if(systemMap.containsKey(key)) {
													//获取该系统的各个费用信息进行更新
													 Map<String, RowMap> systems=systemMap.get(key);
													 if( systems.containsKey(xtmc)) {
														 if ( systemNews.containsKey(xtmc)) {
															 JSONObject system=systemNews.get(xtmc);
															 String XTJG=system.getString("DJ");//系统单价
															 String SWX=system.getString("WXWG");//系统人工费
															 BigDecimal xtold=new BigDecimal(DJ);
															 BigDecimal wxfold=new BigDecimal(SWX);
															//计算新的系统费用信息，价格-模块总价+新的模块总价
															// xtold=xtold.subtract(zjold).add(mndj);															
																 xtold=xtold.subtract(zjold).add(mndj);																															
															 wxfold=wxfold.subtract(zwxold).add(mnwx);
															 system.put("DJ", xtold);
															 system.put("WXWG", wxfold);
															 systemNews.put(xtmc, system);
														 }else {
															 RowMap systemMsg=systems.get(xtbm);
															 String XTJG=systemMsg.getString("DJ");//系统单价
															 String SCLF=systemMsg.getString("CLF");//系统材料费
															 String SRGF=systemMsg.getString("RGF");//系统人工费
															 String SWXWG=systemMsg.getString("WXWG");//系统外协费
															 String SCGF=systemMsg.getString("CGF");//系统采购费
															 BigDecimal xtold=new BigDecimal(DJ);
															 BigDecimal wxfold=new BigDecimal(SWXWG);															 
															//计算新的系统费用信息，价格-模块总价+新的模块总价
															 xtold=xtold.subtract(zjold).add(mndj);															
															 wxfold=wxfold.subtract(zwxold).add(mnwx);
																JSONObject jsonObject=new JSONObject();
															    jsonObject.put("WGXS", systemMsg.getString("WGXS").isEmpty()?"0":systemMsg.getString("WGXS"));//外购系数
																jsonObject.put("ZZXS", systemMsg.getString("ZZXS").isEmpty()?"0":systemMsg.getString("ZZXS"));//外购系数
															    jsonObject.put("DJ", djold);
																jsonObject.put("CLF", SCLF);
																jsonObject.put("RGF", SRGF);
																jsonObject.put("WXWG", wxfold);
																jsonObject.put("CGF",SCGF );
																jsonObject.put("XTMH", xtbm);
																jsonObject.put("XTMC", xtmc);
																jsonObject.put("BINDID", rowMap.getString("BINDID"));
																systemNews.put(xtmc, jsonObject);						
														 }
													 }
												 }
										} catch (Exception e) {
											throw e;
										}finally {
											try {
												workbookwl.close();
											} catch (IOException e) {
												// TODO Auto-generated catch block
												e.printStackTrace();
											}
										}
								 }
								 
							}
					 }
						//更新项目模板费用数据。
					 if(isTrue.equals("1")) {
						 //计算新的制造费，材料费+人工费，乘以制造费比例
						 //String zzfsl=data.getString("ZZFZB").isEmpty()?"0":data.getString("ZZFZB");//制造费比例
						 String zzf=data.getString("ZZF").isEmpty()?"0":data.getString("ZZF");//制造费比例
						 String XMZBJ=data.getString("XMZBJ").isEmpty()?"0":data.getString("XMZBJ");//项目基准价
						// BigDecimal bl=new BigDecimal(zzfsl);	
						 BigDecimal zzfOld=new BigDecimal(zzf);	
						// BigDecimal zzfNew=clfnum.add(rgfnum).multiply(bl);
						// xmcbnum=xmcbnum.subtract(zzfOld).add(zzfNew);
						 String LR=data.getString("LR").isEmpty()?"0":data.getString("LR");//设备利润率
						 String LRJE=data.getString("LRJE").isEmpty()?"0":data.getString("LRJE");//设备利润金额
						 String SBSL=data.getString("SBSL").isEmpty()?"0":data.getString("SBSL");//设备税率
						 String SBSJ=data.getString("SBSJ").isEmpty()?"0":data.getString("SBSJ");//设备税金
						 BigDecimal lr=new BigDecimal(LR);	
						 BigDecimal sblrje=new BigDecimal(LRJE);	
						 BigDecimal sbsl=new BigDecimal(SBSL);	
						 BigDecimal sbsj=new BigDecimal(SBSJ);	
						 BigDecimal jzj=new BigDecimal(XMZBJ);	
						 BigDecimal xmcbOld=new BigDecimal(xmcbfy);	
						 BigDecimal num=new BigDecimal("1");
						 BigDecimal num2=new BigDecimal("100");
						 //新的设备利润 成本+制造费，除以（1-利润率）x利润率
						 BigDecimal sbcblrNew= xmcbnum.add(zzfOld).divide(num.subtract(lr.divide(num2)),4 ).multiply(lr.divide(num2));
						 //新的设备税金
						 BigDecimal sjnew = xmcbnum.add(zzfOld).add(sbcblrNew).multiply(sbsl.divide(num2));
						//设备基准价
						 BigDecimal sbjzj = xmcbnum.add(zzfOld).add(sbcblrNew).add(sjnew);
						 jzj=jzj.subtract(sblrje).subtract(sbsj).subtract(xmcbOld).add(xmcbnum).add(sjnew).add(sbcblrNew);
						 /*
						  * 				 String tempSql="update BO_EU_CPQ_PROJECT_MSG set XMCBYS=?,WXWG=?,XMZBJ=?,LRJE=?,SBSJ=? "
					 		+ " where BINDID=? ";
						  */
						 DBSql.update(tempSql, new Object[] {
								 xmcbnum,wxfnum,jzj,sbcblrNew,sjnew,sbjzj,bindId});	
						 if(!systemNews.isEmpty()) {
							 for (String  bm : systemNews.keySet()) {
								 JSONObject json=systemNews.get(bm);
								 System.out.println("单栋系统信息："+json.toJSONString());
								 String wx=json.getString("WXWG").isEmpty()?"0":json.getString("WXWG");
								 String wg=json.getString("CGF").isEmpty()?"0":json.getString("CGF");
								 String cl=json.getString("CLF").isEmpty()?"0":json.getString("CLF");
								 String rg=json.getString("RGF").isEmpty()?"0":json.getString("RGF");
								 String w=json.getString("WGXS");
								 String z=json.getString("ZZXS");
								 BigDecimal ww=new BigDecimal(w);
								 BigDecimal zz=new BigDecimal(z);
								 BigDecimal num3=new BigDecimal(wx);
								 BigDecimal num4=new BigDecimal(wg);
								 BigDecimal num5=new BigDecimal(wx);
								 BigDecimal num6=new BigDecimal(wg);
								 BigDecimal zj= num3.add(num4).multiply(ww).add(num5.add(num6).multiply(ww));
								 //DBSql.update(systemSql, new Object[] {json.getString("DJ"),cl,rg,wx,wg,zj,bindId,json.getString("XTMC")});	
								 //DBSql.update(systemSql, new Object[] {json.getString("DJ"),cl,rg,wx,wg,zj,bindId,json.getString("XTMC")});	
								 String upSql= "update BO_EU_CPQ_SYSTEM_PRICE set DJ= '"+json.getString("DJ")+"',CLF= '"+cl+"' ,RGF='"+rg+"',WXWG='"+wx+"',CGF='"+wg+"',BJDJG='"+zj+"' where BINDID='"+bindId+"' and XTMC='"+json.getString("XTMC")+"'; ";
									//upSql=String.format(upSql, cldjNew,clfNew,zjNew,zclfNew,ID);
									sqlLists.add(upSql);
							}
						 }
					 }
				 }
				 if(sqlList.size()>0) {
					 String[] array3 = sqlList.toArray(new String[sqlList.size()]);	
					 DBSql.batch(array3);
				}
		} catch (Exception e) {
			throw e;
		}
	}
}
